These are the queries we intended to run over hive.

1) 1st query gets all sensor data history of patient 707

Select 
patient.id , patient.first_name , patient.last_name, patient.contact_number,
patient_bp_sensor.bp , patient_bo_sensor.bo, patient_bg_sensor.bg, patient_temp_sensor.temp, patient_hp_sensor.hp 
from 
patients join patient_bp_sensor on patient.p_id = patient_bp_sensor.p_id LEFT JOIN 
patient_bo_sensor on patient.p_id = patient_bo_sensor.p_id LEFT JOIN 
patient_bg_sensor on patient.p_id = patient_bg_sensor.p_id LEFT JOIN 
patient_hb_sensor on patient.p_id = patient_hb_sensor.p_id LEFT JOIN 
patient_temp_sensor on patient.p_id = patient_temp_sensor.p_id
WHERE patient_id = 707;


2) The 2nd query returns/fetches basic patient information along with entire
sensor data history of every available sensor  of all those patients who had exceptionally high blood pressure on 11th December 2021.  (BP >180)

Select 
patient.p_id , patient.first_name , patient.last_name, patient.contact_number,
patient_bp_sensor.bp , patient_bo_sensor.bo, patient_bg_sensor.bg, patient_temp_sensor.temp, patient_hp_sensor.hp 
from 
patients join patient_bp_sensor on patient.p_id = patient_bp_sensor.p_id LEFT JOIN 
patient_bo_sensor on patient.p_id = patient_bo_sensor.p_id LEFT JOIN 
patient_bg_sensor on patient.p_id = patient_bg_sensor.p_id LEFT JOIN 
patient_hb_sensor on patient.p_id = patient_hb_sensor.p_id LEFT JOIN 
patient_temp_sensor on patient.p_id = patient_temp_sensor.p_id
WHERE patient_id in(
Select DISTINCT patient.p_id
from 
patients  join patient_bp_sensor on patient.p_id = patient_bp_sensor.p_id 
Where CAST(patient_bp_sensor.datetime as date) = 'December 11th 2020 12:00AM' AND patient_bp_sensor.bp >180
);



3) Query 3 counts the number of automatically triggered ambulance requests due to high blood pressure in the month of December 2020

select Count( DISTINCT P_ID) from 
patient_ambulance_request JOIN patient_BP_sensor on
patient_ambulance_request.p_id = patient_BP_sensor.p_id
WHERE
patient_ambulance_request.type = 'automatically triggered' and patient_BP_sensor.bp > 180 AND
 (CAST(patient_ambulance_request.datetime as date) >= 'December 1st 2020 12:00AM' AND
 CAST(patient_ambulance_request.datetime as date) <= 'December 31st 2020 12:00AM')
AND
 (CAST(patient_BP_sensor.datetime as date) >= 'December 1st 2020 12:00AM' AND
 CAST(patient_BP_sensor.datetime as date) <= 'December 31st 2020 12:00AM')
 

 

